import openpyxl
import os

class ExcelCRUD:
    @staticmethod
    def read_excel(file_name, sheet_name):
        """读取数据（查）"""
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        data = [
            [sheet.cell(row=i, column=j).value for j in range(1, sheet.max_column + 1)]
            for i in range(2, sheet.max_row + 1)
        ]
        return data

    @staticmethod
    def add_row(file_name, sheet_name, row_data):
        """新增数据（增）"""
        if not os.path.exists(file_name):
            workbook = openpyxl.Workbook()
            sheet = workbook.active
            sheet.title = sheet_name
            workbook.save(file_name)

        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        sheet.append(row_data)
        workbook.save(file_name)

    @staticmethod
    def update_cell(file_name, sheet_name, row, col, value):
        """更新单元格（改）"""
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        sheet.cell(row=row, column=col, value=value)
        workbook.save(file_name)

    @staticmethod
    def delete_row(file_name, sheet_name, row):
        """删除整行（删）"""
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        sheet.delete_rows(row)
        workbook.save(file_name)


if __name__ == '__main__':
    file = 'user.xlsx'
    sheet = 'user'

    # 增
    ExcelCRUD.add_row(file, sheet, ["张三", 25, "北京"])
    ExcelCRUD.add_row(file, sheet, ["李四", 30, "上海"])

    # 查
    print("读取数据：", ExcelCRUD.read_excel(file, sheet))

    # 改
    ExcelCRUD.update_cell(file, sheet, 2, 2, 28)  # 修改第2行第2列为28

    # 删
    ExcelCRUD.delete_row(file, sheet, 3)  # 删除第3行

    print("最终数据：", ExcelCRUD.read_excel(file, sheet))
